package com.sxfq.loansupermarket.backsystem.modular.refunds.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.*;
import com.sxfq.loansupermarket.backsystem.common.constant.tips.Tip;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.service.MemberProductService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.RechargeInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.service.impl.ResubmitLock;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.SearchRechargeInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.service.IRechargeService;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.ConsultantInfo;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.validation.Valid;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Random;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
/**
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/recharge")
public class RechargeController extends BaseController {
    @Autowired
    IRechargeService service;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private MemberProductService memberProductService;

    @Autowired
    ICustomerService customerService;

    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/balance/";

    /**
     * 跳转到预约客户管理页面
     */
    @RequestMapping("")
    public String recharge(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("auditswitch",redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Auditswitch",0));
        model.addAttribute("sttleAccountsTime", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0));
        return PREFIX + "recharge.html";
    }

    private double subtract(double v1, double v2) {
        BigDecimal b1 = new BigDecimal(Double.toString(v1));
        BigDecimal b2 = new BigDecimal(Double.toString(v2));
        return b1.subtract(b2).doubleValue();
    }

    /**
     * 结账退款列表查询
     */
    @RequestMapping("/rechargeList")
    @ResponseBody
    public Object rechargeList(SearchRechargeInfo info, HttpServletRequest request, Model model) {
        if (info.getAbbreviation() == null || info.getAbbreviation() == "" || info.getAbbreviation() == "null") {
            return null;
        } else {
            Page<RechargeInfo> page = new PageFactory<RechargeInfo>().defaultPage();
            try {
                info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
                info.setLimitIndex(page.getSize());
                page.setCurrent(0);
                List<RechargeInfo> list = service.selectPageListRechargeInfo(page, info);
                if (list.size() == 0) {
                    info.setStartIndex(0);
                    list = service.selectPageListRechargeInfo(page, info);
                }
                page.setTotal(service.getQueryPagesCount(page, info));
                page.setRecords(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return super.packForBT(page);
        }
    }

    /**
     * 添加到店客户
     */
    @RequestMapping(value = "/addRecharge")
    @ResponseBody
    public int addRecharge(@Valid CustomerInfo info, BindingResult result) throws Exception {
        if (result.hasErrors()) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String id = CommUtils.getUUID();
        Double giveAccountBalance = 0.00;
        if (info.getGiveAccountBalance() == null) {
            giveAccountBalance = 0.00;
        } else {
            giveAccountBalance = info.getGiveAccountBalance();
        }
        String sql = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`id`,`customer_id`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`,`give_account_balance`, `record_time`) VALUES" +
                "('" + id + "','" + info.getId() + "'," + 2 + ",'" + info.getReservationsId() + "','" + ShiroKit.getUser().getAccount() + "" +
                "','" + info.getActualAccountBalance() + "'," + giveAccountBalance + ",'" + DateTimeKit.now() + "');";
        return sqlMapper.insert(sql);
    }

    /**
     * 删除
     */
    @RequestMapping(value = "/delete/{id}")
    @ResponseBody
    public Tip delete(@PathVariable String id) throws Exception {
        service.deletes(id);
        return SUCCESS_TIP;
    }

    @RequestMapping(value = "/to_checkoutMethod/{id}")
    public String to_checkoutMethod(@PathVariable String id, Model model) {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select id,reservations_id,customer_id,operation_time,get_time,no_deal_cause from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " where id='" + id + "' limit 1";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        String selectsql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo " +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t1 left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " t2 on t1.pay_service_id=t2.id " +
                " where t1.operation_time='" + rechargeInfo.getOperationTime() + "' " +
                "and t1.reservations_id='" + rechargeInfo.getReservationsId() + "' and  t1.get_time='" + rechargeInfo.getGetTime() + "'";
        List<PayServicedInfo> openBillList = sqlMapper.selectList(selectsql, PayServicedInfo.class);
        Double totleFoldingPay = 0.00;
        Double totleServiceActualPay = 0.00;
        for (PayServicedInfo info : openBillList) {
            if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                totleServiceActualPay += info.getServiceActualPay();
                info.setTotleFoldingPay(totleFoldingPay);
            }
        }
        String sql1 = "select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                "  (CASE t2.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,t2.arrival_store as outpatientName,t2.customer_age as customerAge,t2.customer_phone as customerPhone ," +
                " t2.treatment_unit as treatmentUnit,t2.field_tc as tcName" +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t2 where id='" + rechargeInfo.getCustomerId() + "' limit 1";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql1, ConsultantInfo.class);
        if (ShiroKit.getSessionAttr("checkoutMethodList") == null) {
            String selectsqlCM = "select id,checkout_method as checkoutMethod from checkout_method_info where charge_bill_is_inCash=1 order by num ;";
            ShiroKit.setSessionAttr("checkoutMethodList", sqlMapper.selectList(selectsqlCM));
        }
        String sql22 = "select sum(discount_hide*pay_service_num) as totalDiscountHide,sum(folding_pay) as totalFoldingPay," +
                "whole_discount as wholeDiscount,sum(deduction_amount) as totlDeductionAmount,sum(deposit_amount) as totaldepositAmount," +
                "sum(service_actual_pay) as totalServiceActualPay,is_do as isDo," +
                "IFNULL((select sum(service_actual_pay) from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " where reservations_id='" + rechargeInfo.getReservationsId() + "' " +
                " and operation_time='" + rechargeInfo.getOperationTime() + "' and get_time<'" + rechargeInfo.getGetTime() + "'),0.00 ) as totleServiceActualPay," +
                "IFNULL((select sum(arrears) from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " where reservations_id='" + rechargeInfo.getReservationsId() + "' " +
                " and operation_time='" + rechargeInfo.getOperationTime() + "' and get_time<'" + rechargeInfo.getGetTime() + "'),0.00 ) as totleArrears," +
                "ifnull((select sum(service_actual_pay) from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " where reservations_id='" + rechargeInfo.getReservationsId() + "' " +
                " and operation_time='" + rechargeInfo.getOperationTime() + "' and (is_do=1 or is_do=2 or is_do=4) ),0.00) as totalRenMoney," +
                "(select sum(deposit_amount) from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " where reservations_id='" + rechargeInfo.getReservationsId() + "' " +
                " and operation_time='" + rechargeInfo.getOperationTime() + "' and get_time=operation_time)  as totaldepositAmount2," +
                " (case  " +
                "  when is_do=1 then '不做了' " +
                "  when is_do=2 then '继续做' " +
                "  when is_do=3 then '还欠款'" +
                "  when is_do=4 then '退款'" +
                "  when is_do=5 then '消费'" +
                "  when is_do=6 then '作废' " +
                "  end " +
                " ) isDoName,remarks as remarks from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) +
                " where reservations_id='" + rechargeInfo.getReservationsId() + "' and get_time='" + rechargeInfo.getGetTime() + "' limit 1;";
        PayServiceInfo totalmoney = sqlMapper.selectOne(sql22, PayServiceInfo.class);
        model.addAttribute("id", id);
        model.addAttribute("totalmoney", totalmoney);
        model.addAttribute("openBillList", openBillList);
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("reservationsIdx", rechargeInfo.getNoDealCause());
        model.addAttribute("reservationsId", rechargeInfo.getReservationsId());
        model.addAttribute("customerId", rechargeInfo.getCustomerId());
        model.addAttribute("operationTime", rechargeInfo.getOperationTime());
        model.addAttribute("getTime", rechargeInfo.getGetTime());
        model.addAttribute("totleServiceActualPay", totleServiceActualPay);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("checkoutMethodList", ShiroKit.getSessionAttr("checkoutMethodList"));
        return PREFIX + "checkout_method.html";
    }

    @RequestMapping(value = "/getOpenBillData", method = RequestMethod.POST)
    @ResponseBody
    public int getOpenBillData(String id, HttpServletRequest request) {
        String sql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " where id='" + id + "' limit 1";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        if (rechargeInfo == null) {
            return 0;
        } else {
            return 1;
        }
    }

    private final static Object PRESENT = new Object();

    @RequestMapping(value = "/checkOut", method = RequestMethod.POST)
    @ResponseBody
    @Around("@annotation(com.sxfq.loansupermarket.backsystem.modular.refunds.service.impl.Resubmit)")
    public String checkOut(String reservationsIdx, String customerId, String reservationsId, String[] str, String payorrefund, String orthodontics, double totleServiceActualPay1,
                           String operationTime, String getTime,ProceedingJoinPoint joinPoint) {
        String key = "";
        key = ResubmitLock.handleKey(getTime);
        //执行锁
        boolean lock = false;
        try {
            //设置解锁key
            lock = ResubmitLock.getInstance().lock(key, PRESENT);
            if (lock) {
                //放行
                return service.checkOut(reservationsIdx, customerId, reservationsId, str, payorrefund, orthodontics,totleServiceActualPay1,operationTime, getTime);
            } else {
                //响应重复提交异常
                return "响应重复提交异常";
            }
        } finally {
            //设置解锁key和解锁时间
            ResubmitLock.getInstance().unLock(lock, key, 10);
        }
    }

    /**
     * 统计查询
     */
    @RequestMapping(value = "/rechargeTotal", method = RequestMethod.GET)
    @ResponseBody
    public List<RechargeInfo> rechargeTotal(String startArrivalTimeTwo, String endArrivalTimeTwo,
                                            String dealStatus, String oldOneChannelValue, String reach,
                                            String oldTwoChannelValue, String oldThrChannelValue) throws Exception {
        String contitionInfo1 = " ";
        if (StringUtils.isNoneEmpty(startArrivalTimeTwo)) {  //结账时间
            contitionInfo1 += " and (t3.recharge_time >= '" + startArrivalTimeTwo + "' or t3.recharge_time is null)";
        }
        if (StringUtils.isNoneEmpty(endArrivalTimeTwo)) {
            contitionInfo1 += " and (t3.recharge_time <= '" + endArrivalTimeTwo + "' or t3.recharge_time is null)";
        }
        if (StringUtils.isNoneEmpty(reach)) {  //结账时间
            contitionInfo1 += "  and (t1.customer_name like CONCAT('%','" + reach + "','%') or t1.customer_phone like CONCAT('%','" + reach + "','%') or " +
                    " t1.wechat_number like CONCAT('%','" + reach + "','%') or t1.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or t1.szm like CONCAT('%','" + reach + "','%'))";
        }
        if (StringUtils.isNoneEmpty(dealStatus)) {  //状态
            contitionInfo1 += " and t.deal_Status = '" + dealStatus + "' ";
        }
        if (StringUtils.isNoneEmpty(oldOneChannelValue) && !oldOneChannelValue.equals("请选择")) {
            contitionInfo1 += " and t1.one_channel = '" + oldOneChannelValue + "' ";
        }
        if (StringUtils.isNoneEmpty(oldTwoChannelValue) && !oldTwoChannelValue.equals("请选择")) {
            contitionInfo1 += " and t1.two_Channel = '" + oldTwoChannelValue + "' ";
        }
        if (StringUtils.isNoneEmpty(oldThrChannelValue) && !oldThrChannelValue.equals("请选择")) {
            contitionInfo1 += " and t1.thr_Channel = '" + oldThrChannelValue + "' ";
        }
        contitionInfo1 += "   ";
        String sql = "select  count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney from " +
                "(select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  " +
                "from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " t3 on t.id=t3.deal_id " +
                " left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t1 on t.customer_id=t1.id " +
                " where 1=1 " + contitionInfo1 + " group by t.reservations_id ) t " +

                "union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney " +
                "from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  " +
                "from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " t3 on t.id=t3.deal_id " +
                " left join  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t1 on t.customer_id=t1.id " +
                " where 1=1 " + contitionInfo1 + "  AND t1.customer_status = '新客首次' group by t.reservations_id) t " +

                "union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney " +
                "from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  " +
                "from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " t3 on t.id=t3.deal_id " +
                " left join  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t1 on t.customer_id=t1.id " +
                " where 1=1 " + contitionInfo1 + "  AND t1.customer_status = '新客多次' group by t.reservations_id) t " +

//                " union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney " +
//                "from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  " +
//                "from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " t3 on t.id=t3.deal_id " +
//                " left join  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t1 on t.customer_id=t1.id " +
//                " where 1=1 " + contitionInfo1 + "  AND t1.customer_status  = '复诊' group by t.reservations_id) t " +

                " union all select count(distinct(reservationsId)) as countNum,sum(t.asserviceActualPay) as toalMoney " +
                "from (select t1.reservations_id as reservationsId,t.id, t.service_actual_pay as asserviceActualPay  " +
                "from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " t3 on t.id=t3.deal_id " +
                " left join  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t1 on t.customer_id=t1.id " +
                " where 1=1 " + contitionInfo1 + "  AND t1.customer_status  = '老客' group by t.reservations_id) t ";
        List<RechargeInfo> ToalList = sqlMapper.selectList(sql, RechargeInfo.class);
        return ToalList;
    }

    /**
     * 修改时间  通过客户ID 进行修改
     */
    @RequestMapping(value = "/updateGetTime", method = RequestMethod.POST)
    @ResponseBody
    public int updateGetTime(String reservationsId, String getTime, String updateTime) {
        String sql1 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " t set t.recharge_time = '" + updateTime + "'," +
                " t.recharges_time =null," +
                " t.operation_time = '" + updateTime + "' where t.reservations_Id = '" + reservationsId + "' and t.operation_time ='" + getTime + "'  ";
        sqlMapper.update(sql1);
        String sql = "select * from  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t " +
                "where t.reservations_Id = '" + reservationsId + "' and t.get_time ='" + getTime + "' limit 0,1";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        String sql2 = "";
        String sql11 = "";
        if (rechargeInfo.getOperationTime().equals(rechargeInfo.getGetTime())) {
            sql11 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t set t.operation_time = '" + updateTime + "' " +
                    "where t.reservations_Id = '" + reservationsId + "' and t.operation_time ='" + getTime + "' ";
            sql2 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t set t.get_time = '" + updateTime + "' " +
                    "where t.reservations_Id = '" + reservationsId + "' and t.get_time ='" + getTime + "' ";
            sqlMapper.update(sql11);
        } else {
            sql2 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t set t.get_time = '" + updateTime + "' " +
                    " where t.reservations_Id = '" + reservationsId + "' and t.get_time ='" + getTime + "' ";
        }
        return sqlMapper.update(sql2);
    }

    /**
     * 修改TC  通过客户ID 进行修改
     */
    @RequestMapping(value = "/updateTcName", method = RequestMethod.POST)
    @ResponseBody
    public int updateTcName(String customerId, String updateTcName) {
        String sql2 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " t set t.tc_name = '" + updateTcName + "' where t.customer_id = '" + customerId + "' ";
        String sql1 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " t set t.field_tc = '" + updateTcName + "',t.query_tc='" + updateTcName + "' " +
                "where t.id = '" + customerId + "' ";
        sqlMapper.update(sql1);
        return sqlMapper.update(sql2);
    }

    /**
     * 修改时间跳转界面
     */
    @RequestMapping(value = "/update_time/{ids}")
    public String updateTime(@PathVariable String ids, Model model) {
        String reservationsId = ids.split(",")[0];
        String getTime = ids.split(",")[1];
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        model.addAttribute("getTime", getTime);
        model.addAttribute("reservationsId", reservationsId);
        return PREFIX + "update_time.html";
    }

    /**
     * 修改tcName 跳转界面
     */
    @RequestMapping(value = "/update_tcName/{ids}")
    public String update_tcName(@PathVariable String ids, Model model) {
        String customerId = ids.split(",")[0];
        String tcName = ids.split(",")[1];
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getTcList() == null) {
            String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='" + tableHead + "' and c.name like CONCAT('%','tc','%') ";
            List<TcInfo> tcList = sqlMapper.selectList(getListSql, TcInfo.class);
            ShiroKit.getUser().setTcList(tcList);
        }
        model.addAttribute("tcList", ShiroKit.getUser().getTcList());
        model.addAttribute("tcName", tcName);
        model.addAttribute("customerId", customerId);
        return PREFIX + "update_tcName.html";
    }

    /**
     * 修改时间跳转界面
     */
    @RequestMapping(value = "/update_documentNumber/{ids}")
    public String to_updateDocumentNumber(@PathVariable String ids, Model model) {
        String customerId = ids.split(",")[0];
        String getTime = ids.split(",")[1];
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql1 = "select id as id,customer_name as customerName from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where id='" + customerId + "' limit 1";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql1, CustomerInfo.class);
        String sql = "select id,document_number as documentNumber from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " where customer_id='" + customerId + "' and operation_time='" + getTime + "' limit 0,1";
        RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
        model.addAttribute("rechargeInfo", rechargeInfo);
        model.addAttribute("getTime", getTime);
        model.addAttribute("customerId", customerId);
        model.addAttribute("customerInfo", customerInfo);
        return PREFIX + "update_document_number.html";
    }

    /**
     * 修改结账备注
     */
    @RequestMapping(value = "/updateRechargeRemarks/{ids}")
    public String updateRechargeRemarks(@PathVariable String ids, Model model) {
        String reservationsId = ids.split(",")[0];
        String rechargeTime = ids.split(",")[1];
        String remarksjz = ids.split(",")[2];
        model.addAttribute("remarksjz", remarksjz);
        model.addAttribute("rechargeTime", rechargeTime);
        model.addAttribute("reservationsId", reservationsId);
        return PREFIX + "updateRechargeRemarks.html";
    }

    //修改预约备注
    @RequestMapping(value = "/updateRechargeRemarksOk", method = RequestMethod.POST)
    @ResponseBody
    public int updateRechargeRemarksOk(String reservationsId, String rechargeTime, String remarks) throws Exception {
        String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " set remarks='" + remarks + "'" +
                " where reservations_id='" + reservationsId + "' and recharge_time='" + rechargeTime + "'";
        return sqlMapper.update(sql);
    }

    @RequestMapping(value = "/dealInfo_import")
    public String to_import(Model model) {
        return PREFIX + "dealInfo_import.html";
    }

    /**
     * 导入预约查询明细数据
     *
     * @param model
     * @return
     */
    @RequestMapping(value = "/customerInfo_import")
    public String customerInfoImport(Model model) {
        return PREFIX + "customerInfo_import.html";
    }

    /**
     * 导入预约查询
     */
    @RequestMapping(value = "/uploadLoad")
    public String importUploadLoad(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    DecimalFormat df = new DecimalFormat("0");
                    Cell xh1 = hssfRow.getCell(0); //姓名
                    Cell xh2 = hssfRow.getCell(1);  //病历号
                    Cell xh3 = hssfRow.getCell(2);  //手机号
                    Cell xh4 = hssfRow.getCell(3);//开始时间
                    Cell xh5 = hssfRow.getCell(4);//预约医生
                    Cell xh6 = hssfRow.getCell(5);//咨询项目
                    Cell xh7 = hssfRow.getCell(6); //到店状态
                    Cell xh8 = hssfRow.getCell(7);//创建时间
                    Cell xh9 = hssfRow.getCell(8);//
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
                        xh6s = "null";
                    } else {
                        xh6s = "'" + xh6 + "'";
                    }
                    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11 + "") == null || ("").equals((xh11 + ""))) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    //客户表
                    String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
                    //客户ID
                    String id = CommUtils.getUUID();
                    Random rand = new Random();
                    int randomNumber = rand.nextInt(999999);
                    id = id + tableHead + randomNumber;
                    //预约ID
//                    String reservationId = "";
//                    Integer customerSex = 0;
//                    String selectsql = "select * from  " + tableName + " where medical_record_number=" + xh2s + " limit 0,1;";
//                    String ssql = "select * from  " + tableName + " where customer_phone=" + xh3s + " limit 0,1;";
//                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
//                    CustomerInfo customerInfo1 = sqlMapper.selectOne(ssql, CustomerInfo.class);
//                    if (customerInfo != null) {
//                        reservationId = customerInfo.getReservationsId();
//                        customerSex = customerInfo.getCustomerSex();
//
//                    } else if (customerInfo1 != null) {
//                        reservationId = customerInfo1.getReservationsId();
//                        customerSex = customerInfo1.getCustomerSex();
//
//                    } else {
//                        reservationId = "自到" + CommUtils.getUUID() + randomNumber;
//
//                    }
//                    //yellow_number
//                    String sql1 = " select max(yellow_number) as yellowNumber from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " ";
//                    CustomerInfo customerInfo2 = sqlMapper.selectOne(sql1, CustomerInfo.class);
//                    Integer yellowNumbers = 1;
//                    if (customerInfo != null) {
//                        if (StringUtil.isNotEmpty(customerInfo.getYellowNumber()) && !"null".equals(customerInfo.getYellowNumber())) {
//                            yellowNumbers = Integer.parseInt(customerInfo.getYellowNumber()) + 1;
//                        }
//                    }
                    String eTime = xh4s.replace("'", "").replace("/", "-");
                    String filingTime = xh8s.replace("'", "").replace("/", "-");
                    String eTimes = DateTimeKit.addDate(eTime, 1);
                    String sqladd = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "(id,id22,medical_record_number," +
                            "start_time,doctor,appointment_pay_service,arrival_status," +
                            " filing_time,archiving,scarchiving,remarks,customer_status,field_tc,end_time,customer_name,customer_phone) values " +
                            "('" + id + "','4月1日导入'," + xh2s + "," +
                            "" + xh4s + "," + xh5s + "," + xh6s + "," + xh7s + "," +
                            " '" + filingTime + "'," + xh9s + ", " + xh10s + "," + xh11s + "," + xh12s + "," + xh13s + ",'" + eTimes + "'" +
                            "," + xh1s + "," + xh3s + ");";
                    sqlMapper.insert(sqladd);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }

    /**
     * 导入 e看牙
     */
    @RequestMapping(value = "/upload1")
    public String importDealInfo(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    Cell xh9 = hssfRow.getCell(8);
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);//收费项目
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);//单价
                    Cell xh14 = hssfRow.getCell(13);//应收
                    Cell xh15 = hssfRow.getCell(14);//实收
                    Cell xh16 = hssfRow.getCell(15);
                    Cell xh17 = hssfRow.getCell(16);
                    Cell xh18 = hssfRow.getCell(17);//现金
                    Cell xh19 = hssfRow.getCell(18);//银行卡
                    Cell xh20 = hssfRow.getCell(19);
                    Cell xh21 = hssfRow.getCell(20);
                    Cell xh22 = hssfRow.getCell(21);
                    Cell xh23 = hssfRow.getCell(22);
                    Cell xh24 = hssfRow.getCell(23);
                    Cell xh25 = hssfRow.getCell(24);
                    Cell xh26 = hssfRow.getCell(25);
                    Cell xh27 = hssfRow.getCell(26);
                    Cell xh28 = hssfRow.getCell(27);
                    Cell xh29 = hssfRow.getCell(28);
                    Cell xh30 = hssfRow.getCell(29);
                    Cell xh31 = hssfRow.getCell(30);
                    Cell xh32 = hssfRow.getCell(31);
                    Cell xh33 = hssfRow.getCell(32);
                    Cell xh34 = hssfRow.getCell(33);
                    Cell xh35 = hssfRow.getCell(34);
                    Cell xh36 = hssfRow.getCell(35);
                    Cell xh37 = hssfRow.getCell(36);
                    Cell xh38 = hssfRow.getCell(37);
                    Cell xh39 = hssfRow.getCell(38);
                    Cell xh40 = hssfRow.getCell(39);
                    Cell xh41 = hssfRow.getCell(40);
                    Cell xh42 = hssfRow.getCell(41);
                    Cell xh43 = hssfRow.getCell(42);
                    Cell xh44 = hssfRow.getCell(43);
                    Cell xh45 = hssfRow.getCell(44);
                    Cell xh46 = hssfRow.getCell(45);
                    Cell xh47 = hssfRow.getCell(46);
                    Cell xh48 = hssfRow.getCell(47);
                    Cell xh49 = hssfRow.getCell(48);
                    Cell xh50 = hssfRow.getCell(49);
                    Cell xh51 = hssfRow.getCell(50);
                    Cell xh52 = hssfRow.getCell(51);
                    Cell xh53 = hssfRow.getCell(52);
                    Cell xh54 = hssfRow.getCell(53);
                    Cell xh55 = hssfRow.getCell(54);
                    Cell xh56 = hssfRow.getCell(55);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    String xh15s = "";
                    String xh16s = "";
                    String xh17s = "";
                    String xh18s = "";
                    String xh19s = "";
                    String xh20s = "";
                    String xh21s = "";
                    String xh22s = "";
                    String xh23s = "";
                    String xh24s = "";
                    String xh25s = "";
                    String xh26s = "";
                    String xh27s = "";
                    String xh28s = "";
                    String xh29s = "";
                    String xh30s = "";
                    String xh31s = "";
                    String xh32s = "";
                    String xh33s = "";
                    String xh34s = "";
                    String xh35s = "";
                    String xh36s = "";
                    String xh37s = "";
                    String xh38s = "";
                    String xh39s = "";
                    String xh40s = "";
                    String xh41s = "";
                    String xh42s = "";
                    String xh43s = "";
                    String xh44s = "";
                    String xh45s = "";
                    String xh46s = "";
                    String xh47s = "";
                    String xh48s = "";
                    String xh49s = "";
                    String xh50s = "";
                    String xh51s = "";
                    String xh52s = "";
                    String xh53s = "";
                    String xh54s = "";
                    String xh55s = "";
                    String xh56s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "" + xh2 + "";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
                        xh6s = "null";
                    } else {
                        xh6s = "'" + xh6 + "'";
                    }
                    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11 + "") == null || ("").equals((xh11 + ""))) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    if ((xh14 + "") == null || ("").equals((xh14 + ""))) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }
                    if ((xh15 + "") == null || ("").equals((xh15 + ""))) {
                        xh15s = "null";
                    } else {
                        xh15s = "'" + xh15 + "'";
                    }
                    if ((xh16 + "") == null || ("").equals((xh16 + ""))) {
                        xh16s = "null";
                    } else {
                        xh16s = "'" + xh16 + "'";
                    }
                    if ((xh17 + "") == null || ("").equals((xh17 + ""))) {
                        xh17s = "null";
                    } else {
                        xh17s = "'" + xh17 + "'";
                    }
                    if ((xh18 + "") == null || ("").equals((xh18 + ""))) {
                        xh18s = "null";
                    } else {
                        xh18s = "'" + xh18 + "'";
                    }
                    if ((xh19 + "") == null || ("").equals((xh19 + ""))) {
                        xh19s = "null";
                    } else {
                        xh19s = "'" + xh19 + "'";
                    }
                    if ((xh20 + "") == null || ("").equals((xh20 + ""))) {
                        xh20s = "null";
                    } else {
                        xh20s = "'" + xh20 + "'";
                    }
                    if ((xh21 + "") == null || ("").equals((xh21 + ""))) {
                        xh21s = "null";
                    } else {
                        xh21s = "'" + xh21 + "'";
                    }
                    if ((xh22 + "") == null || ("").equals((xh22 + ""))) {
                        xh22s = "null";
                    } else {
                        xh22s = "'" + xh22 + "'";
                    }
                    if ((xh23 + "") == null || ("").equals((xh23 + ""))) {
                        xh23s = "null";
                    } else {
                        xh23s = "'" + xh23 + "'";
                    }
                    if ((xh24 + "") == null || ("").equals((xh24 + ""))) {
                        xh24s = "null";
                    } else {
                        xh24s = "'" + xh24 + "'";
                    }
                    if ((xh25 + "") == null || ("").equals((xh25 + ""))) {
                        xh25s = "null";
                    } else {
                        xh25s = "'" + xh25 + "'";
                    }
                    if ((xh26 + "") == null || ("").equals((xh26 + ""))) {
                        xh26s = "null";
                    } else {
                        xh26s = "'" + xh26 + "'";
                    }
                    if ((xh27 + "") == null || ("").equals((xh27 + ""))) {
                        xh27s = "null";
                    } else {
                        xh27s = "'" + xh27 + "'";
                    }
                    if ((xh28 + "") == null || ("").equals((xh28 + ""))) {
                        xh28s = "null";
                    } else {
                        xh28s = "'" + xh28 + "'";
                    }
                    if ((xh29 + "") == null || ("").equals((xh29 + ""))) {
                        xh29s = "null";
                    } else {
                        xh29s = "'" + xh29 + "'";
                    }
                    if ((xh30 + "") == null || ("").equals((xh30 + ""))) {
                        xh30s = "null";
                    } else {
                        xh30s = "'" + xh30 + "'";
                    }
                    if ((xh31 + "") == null || ("").equals((xh31 + ""))) {
                        xh31s = "null";
                    } else {
                        xh31s = "'" + xh31 + "'";
                    }
                    if ((xh32 + "") == null || ("").equals((xh32 + ""))) {
                        xh32s = "null";
                    } else {
                        xh32s = "'" + xh32 + "'";
                    }
                    if ((xh33 + "") == null || ("").equals((xh33 + ""))) {
                        xh33s = "null";
                    } else {
                        xh33s = "'" + xh33 + "'";
                    }
                    if ((xh34 + "") == null || ("").equals((xh34 + ""))) {
                        xh34s = "null";
                    } else {
                        xh34s = "'" + xh34 + "'";
                    }
                    if ((xh35 + "") == null || ("").equals((xh35 + ""))) {
                        xh35s = "null";
                    } else {
                        xh35s = "'" + xh35 + "'";
                    }
                    if ((xh36 + "") == null || ("").equals((xh36 + ""))) {
                        xh36s = "null";
                    } else {
                        xh36s = "'" + xh36 + "'";
                    }
                    if ((xh37 + "") == null || ("").equals((xh37 + ""))) {
                        xh37s = "null";
                    } else {
                        xh37s = "'" + xh37 + "'";
                    }
                    if ((xh38 + "") == null || ("").equals((xh38 + ""))) {
                        xh38s = "null";
                    } else {
                        xh38s = "'" + xh38 + "'";
                    }
                    if ((xh39 + "") == null || ("").equals((xh39 + ""))) {
                        xh39s = "null";
                    } else {
                        xh39s = "'" + xh39 + "'";
                    }
                    if ((xh40 + "") == null || ("").equals((xh40 + ""))) {
                        xh40s = "null";
                    } else {
                        xh40s = "'" + xh40 + "'";
                    }
                    if ((xh41 + "") == null || ("").equals((xh41 + ""))) {
                        xh41s = "null";
                    } else {
                        xh41s = "'" + xh41 + "'";
                    }
                    if ((xh42 + "") == null || ("").equals((xh42 + ""))) {
                        xh42s = "null";
                    } else {
                        xh42s = "'" + xh42 + "'";
                    }
                    if ((xh43 + "") == null || ("").equals((xh43 + ""))) {
                        xh43s = "null";
                    } else {
                        xh43s = "'" + xh43 + "'";
                    }
                    if ((xh44 + "") == null || ("").equals((xh44 + ""))) {
                        xh44s = "null";
                    } else {
                        xh44s = "'" + xh44 + "'";
                    }
                    if ((xh45 + "") == null || ("").equals((xh45 + ""))) {
                        xh45s = "null";
                    } else {
                        xh45s = "'" + xh45 + "'";
                    }
                    if ((xh46 + "") == null || ("").equals((xh46 + ""))) {
                        xh46s = "null";
                    } else {
                        xh46s = "'" + xh46 + "'";
                    }
                    if ((xh47 + "") == null || ("").equals((xh47 + ""))) {
                        xh47s = "null";
                    } else {
                        xh47s = "'" + xh47 + "'";
                    }
                    if ((xh48 + "") == null || ("").equals((xh48 + ""))) {
                        xh48s = "null";
                    } else {
                        xh48s = "'" + xh48 + "'";
                    }
                    if ((xh49 + "") == null || ("").equals((xh49 + ""))) {
                        xh49s = "null";
                    } else {
                        xh49s = "'" + xh49 + "'";
                    }
                    if ((xh50 + "") == null || ("").equals((xh50 + ""))) {
                        xh50s = "null";
                    } else {
                        xh50s = "'" + xh50 + "'";
                    }
                    if ((xh51 + "") == null || ("").equals((xh51 + ""))) {
                        xh51s = "null";
                    } else {
                        xh51s = "'" + xh51 + "'";
                    }
                    if ((xh52 + "") == null || ("").equals((xh52 + ""))) {
                        xh52s = "null";
                    } else {
                        xh52s = "'" + xh52 + "'";
                    }
                    if ((xh53 + "") == null || ("").equals((xh53 + ""))) {
                        xh53s = "null";
                    } else {
                        xh53s = "'" + xh53 + "'";
                    }
                    if ((xh54 + "") == null || ("").equals((xh54 + ""))) {
                        xh54s = "null";
                    } else {
                        xh54s = "'" + xh54 + "'";
                    }
                    if ((xh55 + "") == null || ("").equals((xh55 + ""))) {
                        xh55s = "null";
                    } else {
                        xh55s = "'" + xh55 + "'";
                    }
                    if ((xh56 + "") == null || ("").equals((xh56 + ""))) {
                        xh56s = "null";
                    } else {
                        xh56s = "'" + xh56 + "'";
                    }
                    String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
                    Random rands = new Random();
                    int randomNumber = rands.nextInt(999999);
                    String a1 = (xh9s.replace("'", "")).replace("-", "");
                    String a2 = a1.replace(" ", "");
                    String a3 = a2.replace(":", "");
                    String a4 = tableHead.replace("_", "");
                    String time = (xh9s.replace("'", "")).substring(0, 10);
                    String idd = "";
                    idd = a4 + randomNumber + a3;
                    //客户信息
                    String id = "";
                    String reservationsId = "";
                    String selectsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where medical_record_number=" + xh1s + " " +
                            " and arrival_time is not null order by arrival_time desc limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    if (customerInfo == null) {
                        String selectsql11 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where customer_name=" + xh4s + "" +
                                " and customer_phone=" + xh7s + " and arrival_time is not null order by arrival_time desc limit 0,1;";
                        customerInfo = sqlMapper.selectOne(selectsql11, CustomerInfo.class);
                    }
                    String selectsql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where medical_record_number=" + xh1s + " " +
                            " and left(arrival_time,10)='" + time + "' limit 0,1;";  //到店时间等于结账时间
                    CustomerInfo customerInfo1 = sqlMapper.selectOne(selectsql1, CustomerInfo.class);
                    if (customerInfo1 == null) {
                        String selectsql11 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where customer_name=" + xh4s + "" +
                                " and customer_phone=" + xh7s + " and left(arrival_time,10)='" + time + "' limit 0,1;";  //到店时间等于结账时间
                        customerInfo1 = sqlMapper.selectOne(selectsql11, CustomerInfo.class);
                    }
                    if (customerInfo1 == null) {
                        if (customerInfo == null) {
                        } else {
                            customerInfo1 = customerInfo;
                        }
                    }
                    if (customerInfo1 == null || customerInfo1.equals("")) {
                        id = idd;
                        reservationsId = "预约" + idd;
                    } else {
                        id = customerInfo.getId();
                        reservationsId = customerInfo.getReservationsId();
                    }
                    String payId = "";
                    String id2 = "";
                    String deal_id = "";
                    //收费项目
                    String sqlPayService = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " " +
                            "where pay_service_name=" + xh16s + " order by grade desc limit 0,1;";
                    PayServiceInfo payService = sqlMapper.selectOne(sqlPayService, PayServiceInfo.class);
                    if (payService == null) {
                        String insertsql = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " (`num`,`department`, `first_level_project`, " +
                                "`pay_service_name`, `pay_service_price_one`, `pay_service_price_two`,is_upper,remarks) VALUES" +
                                "(  " + 807 + " ,'wd导入'," + xh15s + " " +
                                "," + xh16s + "," + xh18s + "," + xh18s + ",0 ,'8月7万达导入e看牙');";
                        sqlMapper.insert(insertsql);
                        String sqlPayService1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " " +
                                "where pay_service_name=" + xh16s + " order by grade desc limit 0,1;";
                        PayServiceInfo payService1 = sqlMapper.selectOne(sqlPayService1, PayServiceInfo.class);
                        payId = payService1.getId();
                    } else {
                        payId = payService.getId();
                    }
                    //收费类型
                    if ("'收费'".equals(xh8s)) {
                        xh8s = "'5'";
                    } else if ("'退费'".equals(xh8s)) {
                        xh8s = "'4'";
                    } else if ("'收欠费'".equals(xh8s)) {
                        xh8s = "'3'";
                    } else if ("'部分退费'".equals(xh8s)) {
                        xh8s = "'4'";
                    } else if ("'部分退费(作废)'".equals(xh8s)) {
                        xh8s = "'6'";
                    } else if ("'作废'".equals(xh8s)) {
                        xh8s = "'6'";
                    } else if ("'收费(作废)'".equals(xh8s)) {
                        xh8s = "'6'";
                    } else if ("'收欠费(作废)'".equals(xh8s)) {
                        xh8s = "'6'";
                    } else {
                        xh8s = "'0'";
                    }
                    RechargeInfo rechargeInfo = null;
                    if (xh8s == "'4'" || xh8s == "'3'") {
                        String selectotionsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " a where a.reservations_id='" + reservationsId + "' " +
                                "and pay_service_id ='" + payId + "' and operation_time=get_time limit 1";
                        rechargeInfo = sqlMapper.selectOne(selectotionsql, RechargeInfo.class);
                    }
                    String sql = "";
                    String deductionType = "";
                    String deductionAmount = "0";
                    /*if (!"'0.0'".equals(xh43s)) {
                        deductionType = "积分";
                        deductionAmount = xh43s;
                    }else if (!"'0.0'".equals(xh41s) ) {
                        deductionType = "优惠券";
                        deductionAmount = xh41s;
                    }else if (!"'0.0'".equals(xh42s)) {
                        deductionType = "优惠券";
                        deductionAmount = xh42s;
                    }*/
                    if (rechargeInfo != null) {
                        if (customerInfo1 != null) {
                            randomNumber = randomNumber + 1;
                            idd = rechargeInfo.getId2() + randomNumber;
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time,get_time,remarks,doctor,nurse,orthodontic_number," +
                                    "whole_discount,id2,deduction_type,deduction_amount ) VALUES " +
                                    "('" + idd + "' , " + xh8s + " , '" + customerInfo1.getId() + "' , '万达店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh12s + "," + xh17s + " , " +
                                    " " + xh22s + " , " + xh21s + " , " + xh18s + ", " + xh23s + " ,'" + rechargeInfo.getOperationTime() + "' ,  " +
                                    " " + xh9s + " ," + xh24s + " , " + xh10s + " , " + xh11s + " , " + xh6s + " , " + xh20s + " , " +
                                    " '" + rechargeInfo.getId2() + "','" + deductionType + "'," + deductionAmount + ");";
                            id = customerInfo1.getId();
                            id2 = rechargeInfo.getId2();
                            deal_id = idd;
                        } else {
                            randomNumber = randomNumber + 1;
                            idd = rechargeInfo.getId2() + randomNumber;
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time,get_time,remarks,doctor,nurse,orthodontic_number," +
                                    "whole_discount,id2,deduction_type,deduction_amount ) VALUES " +
                                    "('" + idd + "' , " + xh8s + " , '" + idd + "' , '万达店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh12s + "," + xh17s + " , " +
                                    " " + xh22s + " , " + xh21s + " , " + xh18s + ", " + xh23s + " ,'" + rechargeInfo.getOperationTime() + "' ,  " +
                                    " " + xh9s + " ," + xh24s + " , " + xh10s + " , " + xh11s + " , " + xh6s + " , " + xh20s + " , " +
                                    " '" + rechargeInfo.getId2() + "','" + deductionType + "'," + deductionAmount + ");";
                            id = idd;
                            id2 = rechargeInfo.getId2();
                            deal_id = idd;
                        }
                    } else {
                        if (customerInfo1 == null) {
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time,get_time,remarks,doctor,nurse,orthodontic_number," +
                                    "whole_discount,id2,deduction_type,deduction_amount ) VALUES " +
                                    "('" + idd + "' , " + xh8s + " , '" + id + "' , '万达店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh12s + "," + xh17s + " , " +
                                    " " + xh22s + " , " + xh21s + " , " + xh18s + ", " + xh23s + " ," + xh9s + " ,  " +
                                    " " + xh9s + " ," + xh24s + " , " + xh10s + " , " + xh11s + " , " + xh6s + " , " + xh20s + " , " +
                                    " '" + idd + "','" + deductionType + "'," + deductionAmount + ");";
                            id2 = idd;
                            deal_id = idd;
                        } else {
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time,get_time,remarks,doctor,nurse,orthodontic_number," +
                                    "whole_discount,id2,deduction_type,deduction_amount ) VALUES " +
                                    "('" + idd + "' , " + xh8s + " , '" + customerInfo1.getId() + "' , '万达店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh12s + "," + xh17s + " , " +
                                    " " + xh22s + " , " + xh21s + " , " + xh18s + ", " + xh23s + " ," + xh9s + " ,  " +
                                    " " + xh9s + " ," + xh24s + " , " + xh10s + " , " + xh11s + " , " + xh6s + " ," + xh20s + " , " +
                                    " '" + idd + "','" + deductionType + "'," + deductionAmount + ");";
                            id = customerInfo1.getId();
                            id2 = idd;
                            deal_id = idd;
                        }
                    }
                    int count = 0;
                    if (!"'0.0'".equals(xh25s)) {
                        String xh19ss = CommUtils.getUUID();
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (id,`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "('" + xh19ss + "'," + xh13s + ",'" + id + "','美团点评'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh25s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh26s)) {
                        String xh19ss = CommUtils.getUUID();
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (id,`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "('" + xh19ss + "'," + xh13s + ",'" + id + "','社保卡'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh26s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh27s)) {
                        String xh19ss = CommUtils.getUUID();
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (id,`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "('" + xh19ss + "'," + xh13s + ",'" + id + "','电子扫码'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh27s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh28s)) {
                        String xh19ss = CommUtils.getUUID();
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (id,`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "('" + xh19ss + "'," + xh13s + ",'" + id + "','现金'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh28s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh29s)) {
                        String xh19ss = CommUtils.getUUID();
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (id,`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "('" + xh19ss + "'," + xh13s + ",'" + id + "','即分期'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh29s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
//                    if (!"'0.0'".equals(xh30s)) {
//                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
//                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
//                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
//                                "(" + xh13s + ",'" + id + "','微信支付'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
//                                "," + xh30s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
//                        sqlMapper.insert(sqlinsert1);
//                        count++;
//                    }
//                    if (!"'0.0'".equals(xh31s)) {
//                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
//                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
//                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
//                                "(" + xh13s + ",'" + id + "','美团'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
//                                "," + xh31s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
//                        sqlMapper.insert(sqlinsert1);
//                        count++;
//                    }
//                    if (!"'0.0'".equals(xh32s)) {
//                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
//                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
//                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
//                                "(" + xh13s + ",'" + id + "','美团点评'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
//                                "," + xh32s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
//                        sqlMapper.insert(sqlinsert1);
//                        count++;
//                    }
//                    if (!"'0.0'".equals(xh33s)) {
//                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
//                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
//                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
//                                "(" + xh13s + ",'" + id + "','电子扫码'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
//                                "," + xh33s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
//                        sqlMapper.insert(sqlinsert1);
//                        count++;
//                    }
//                    if (!"'0.0'".equals(xh34s)) {
//                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
//                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
//                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
//                                "(" + xh13s + ",'" + id + "','即分期'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
//                                "," + xh34s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
//                        sqlMapper.insert(sqlinsert1);
//                        count++;
//                    }
                   /*
                    if (!"'0.0'".equals(xh35s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','华银百汇扫码'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh35s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh36s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','百度糯米'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh36s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh37s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','储值卡馈赠金'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh37s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh38s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','储值卡本金'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh38s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh39s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','随行付'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh39s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh40s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','淘宝团购'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh40s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh41s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','优惠券'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh41s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',3);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh42s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','优惠券'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh42s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',3);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh43s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','积分'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh43s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',3);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh44s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','微信公众号'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh44s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh45s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','微信支付'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh45s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh46s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','电子扫码收款'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh46s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh47s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','华荣时代'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh47s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh48s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','精选武汉'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh48s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh49s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','平安合作洁牙'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh49s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh50s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','嘉联POS'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh50s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh51s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','天猫'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh51s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh52s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','即分期'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh52s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh53s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','华夏POS'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh53s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh54s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','武汉更划算'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh54s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh55s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','阿里健康'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh55s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh56s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh13s + ",'" + id + "','蚂蚁生活'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh56s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }*/

                    if (count == 0) {
                        String xh19ss = CommUtils.getUUID();
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (id,`recharge_people`,`customer_id`,`checkout_method`," +
                                "`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "('" + xh19ss + "'," + xh13s + ",'" + id + "','现金'," + 2 + ",'" + reservationsId + "'," + xh13s + " " +
                                "," + xh22s + "," + xh9s + "," + xh9s + "," + xh24s + " ," + xh3s + ",'" + id2 + "','" + deal_id + "',1);";
                        sqlMapper.insert(sqlinsert1);
                    }
                    sqlMapper.insert(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }

    /**
     * 导入 牙医管家
     */
    @RequestMapping(value = "/upload")
    public String importDealInfo2(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);
                    Cell xh5 = hssfRow.getCell(4);
                    Cell xh6 = hssfRow.getCell(5);
                    Cell xh7 = hssfRow.getCell(6);
                    Cell xh8 = hssfRow.getCell(7);
                    Cell xh9 = hssfRow.getCell(8);
                    Cell xh10 = hssfRow.getCell(9);
                    Cell xh11 = hssfRow.getCell(10);//收费项目
                    Cell xh12 = hssfRow.getCell(11);
                    Cell xh13 = hssfRow.getCell(12);//单价
                    Cell xh14 = hssfRow.getCell(13);//应收
                    Cell xh15 = hssfRow.getCell(14);//实收
                    Cell xh16 = hssfRow.getCell(15);
                    Cell xh17 = hssfRow.getCell(16);
                    Cell xh18 = hssfRow.getCell(17);//现金
                    Cell xh19 = hssfRow.getCell(18);//银行卡
                    Cell xh20 = hssfRow.getCell(19);
                    Cell xh21 = hssfRow.getCell(20);
                    Cell xh22 = hssfRow.getCell(21);
                    Cell xh23 = hssfRow.getCell(22);
                    Cell xh24 = hssfRow.getCell(23);
                    Cell xh25 = hssfRow.getCell(24);
                    Cell xh26 = hssfRow.getCell(25);
                    Cell xh27 = hssfRow.getCell(26);
                    Cell xh28 = hssfRow.getCell(27);
                    Cell xh29 = hssfRow.getCell(28);
                    Cell xh30 = hssfRow.getCell(29);
                    Cell xh31 = hssfRow.getCell(30);
                    Cell xh32 = hssfRow.getCell(31);
                    Cell xh33 = hssfRow.getCell(32);
                    Cell xh34 = hssfRow.getCell(33);
                    Cell xh35 = hssfRow.getCell(34);
                    Cell xh36 = hssfRow.getCell(35);
                    Cell xh37 = hssfRow.getCell(36);
                    Cell xh38 = hssfRow.getCell(37);
                    Cell xh39 = hssfRow.getCell(38);
                    Cell xh40 = hssfRow.getCell(39);
                    Cell xh41 = hssfRow.getCell(40);
                    Cell xh42 = hssfRow.getCell(41);
                    Cell xh43 = hssfRow.getCell(42);
                    Cell xh44 = hssfRow.getCell(43);
                    Cell xh45 = hssfRow.getCell(44);
                    Cell xh46 = hssfRow.getCell(45);
                    Cell xh47 = hssfRow.getCell(46);
                    Cell xh48 = hssfRow.getCell(47);
                    Cell xh49 = hssfRow.getCell(48);
                    Cell xh50 = hssfRow.getCell(49);
                    Cell xh51 = hssfRow.getCell(50);
                    Cell xh52 = hssfRow.getCell(51);
                    Cell xh53 = hssfRow.getCell(52);
                    Cell xh54 = hssfRow.getCell(53);
                    Cell xh55 = hssfRow.getCell(54);
                    Cell xh56 = hssfRow.getCell(55);
                    Cell xh57 = hssfRow.getCell(56);
                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";
                    String xh5s = "";
                    String xh6s = "";
                    String xh7s = "";
                    String xh8s = "";
                    String xh9s = "";
                    String xh10s = "";
                    String xh11s = "";
                    String xh12s = "";
                    String xh13s = "";
                    String xh14s = "";
                    String xh15s = "";
                    String xh16s = "";
                    String xh17s = "";
                    String xh18s = "";
                    String xh19s = "";
                    String xh20s = "";
                    String xh21s = "";
                    String xh22s = "";
                    String xh23s = "";
                    String xh24s = "";
                    String xh25s = "";
                    String xh26s = "";
                    String xh27s = "";
                    String xh28s = "";
                    String xh29s = "";
                    String xh30s = "";
                    String xh31s = "";
                    String xh32s = "";
                    String xh33s = "";
                    String xh34s = "";
                    String xh35s = "";
                    String xh36s = "";
                    String xh37s = "";
                    String xh38s = "";
                    String xh39s = "";
                    String xh40s = "";
                    String xh41s = "";
                    String xh42s = "";
                    String xh43s = "";
                    String xh44s = "";
                    String xh45s = "";
                    String xh46s = "";
                    String xh47s = "";
                    String xh48s = "";
                    String xh49s = "";
                    String xh50s = "";
                    String xh51s = "";
                    String xh52s = "";
                    String xh53s = "";
                    String xh54s = "";
                    String xh55s = "";
                    String xh56s = "";
                    String xh57s = "";
                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "" + xh2 + "";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }
                    if ((xh5 + "") == null || ("").equals((xh5 + ""))) {
                        xh5s = "null";
                    } else {
                        xh5s = "'" + xh5 + "'";
                    }
                    if ((xh6 + "") == null || ("").equals((xh6 + ""))) {
                        xh6s = "null";
                    } else {
                        xh6s = "'" + xh6 + "'";
                    }
                    if ((xh7 + "") == null || ("").equals((xh7 + ""))) {
                        xh7s = "null";
                    } else {
                        xh7s = "'" + xh7 + "'";
                    }
                    if ((xh8 + "") == null || ("").equals((xh8 + ""))) {
                        xh8s = "null";
                    } else {
                        xh8s = "'" + xh8 + "'";
                    }
                    if ((xh9 + "") == null || ("").equals((xh9 + ""))) {
                        xh9s = "null";
                    } else {
                        xh9s = "'" + xh9 + "'";
                    }
                    if ((xh10 + "") == null || ("").equals((xh10 + ""))) {
                        xh10s = "null";
                    } else {
                        xh10s = "'" + xh10 + "'";
                    }
                    if ((xh11 + "") == null || ("").equals((xh11 + ""))) {
                        xh11s = "null";
                    } else {
                        xh11s = "'" + xh11 + "'";
                    }
                    if ((xh12 + "") == null || ("").equals((xh12 + ""))) {
                        xh12s = "null";
                    } else {
                        xh12s = "'" + xh12 + "'";
                    }
                    if ((xh13 + "") == null || ("").equals((xh13 + ""))) {
                        xh13s = "null";
                    } else {
                        xh13s = "'" + xh13 + "'";
                    }
                    if ((xh14 + "") == null || ("").equals((xh14 + ""))) {
                        xh14s = "null";
                    } else {
                        xh14s = "'" + xh14 + "'";
                    }
                    if ((xh15 + "") == null || ("").equals((xh15 + ""))) {
                        xh15s = "null";
                    } else {
                        xh15s = "'" + xh15 + "'";
                    }
                    if ((xh16 + "") == null || ("").equals((xh16 + ""))) {
                        xh16s = "null";
                    } else {
                        xh16s = "'" + xh16 + "'";
                    }
                    if ((xh17 + "") == null || ("").equals((xh17 + ""))) {
                        xh17s = "null";
                    } else {
                        xh17s = "'" + xh17 + "'";
                    }
                    if ((xh18 + "") == null || ("").equals((xh18 + ""))) {
                        xh18s = "null";
                    } else {
                        xh18s = "'" + xh18 + "'";
                    }
                    if ((xh19 + "") == null || ("").equals((xh19 + ""))) {
                        xh19s = "null";
                    } else {
                        xh19s = "'" + xh19 + "'";
                    }
                    if ((xh20 + "") == null || ("").equals((xh20 + ""))) {
                        xh20s = "null";
                    } else {
                        xh20s = "'" + xh20 + "'";
                    }
                    if ((xh21 + "") == null || ("").equals((xh21 + ""))) {
                        xh21s = "null";
                    } else {
                        xh21s = "'" + xh21 + "'";
                    }
                    if ((xh22 + "") == null || ("").equals((xh22 + ""))) {
                        xh22s = "null";
                    } else {
                        xh22s = "'" + xh22 + "'";
                    }
                    if ((xh23 + "") == null || ("").equals((xh23 + ""))) {
                        xh23s = "null";
                    } else {
                        xh23s = "'" + xh23 + "'";
                    }
                    if ((xh24 + "") == null || ("").equals((xh24 + ""))) {
                        xh24s = "null";
                    } else {
                        xh24s = "'" + xh24 + "'";
                    }
                    if ((xh25 + "") == null || ("").equals((xh25 + ""))) {
                        xh25s = "null";
                    } else {
                        xh25s = "'" + xh25 + "'";
                    }
                    if ((xh26 + "") == null || ("").equals((xh26 + ""))) {
                        xh26s = "null";
                    } else {
                        xh26s = "'" + xh26 + "'";
                    }
                    if ((xh27 + "") == null || ("").equals((xh27 + ""))) {
                        xh27s = "null";
                    } else {
                        xh27s = "'" + xh27 + "'";
                    }
                    if ((xh28 + "") == null || ("").equals((xh28 + ""))) {
                        xh28s = "null";
                    } else {
                        xh28s = "'" + xh28 + "'";
                    }
                    if ((xh29 + "") == null || ("").equals((xh29 + ""))) {
                        xh29s = "null";
                    } else {
                        xh29s = "'" + xh29 + "'";
                    }
                    if ((xh30 + "") == null || ("").equals((xh30 + ""))) {
                        xh30s = "null";
                    } else {
                        xh30s = "'" + xh30 + "'";
                    }
                    if ((xh31 + "") == null || ("").equals((xh31 + ""))) {
                        xh31s = "null";
                    } else {
                        xh31s = "'" + xh31 + "'";
                    }
                    if ((xh32 + "") == null || ("").equals((xh32 + ""))) {
                        xh32s = "null";
                    } else {
                        xh32s = "'" + xh32 + "'";
                    }
                    if ((xh33 + "") == null || ("").equals((xh33 + ""))) {
                        xh33s = "null";
                    } else {
                        xh33s = "'" + xh33 + "'";
                    }
                    if ((xh34 + "") == null || ("").equals((xh34 + ""))) {
                        xh34s = "null";
                    } else {
                        xh34s = "'" + xh34 + "'";
                    }
                    if ((xh35 + "") == null || ("").equals((xh35 + ""))) {
                        xh35s = "null";
                    } else {
                        xh35s = "'" + xh35 + "'";
                    }
                    if ((xh36 + "") == null || ("").equals((xh36 + ""))) {
                        xh36s = "null";
                    } else {
                        xh36s = "'" + xh36 + "'";
                    }
                    if ((xh37 + "") == null || ("").equals((xh37 + ""))) {
                        xh37s = "null";
                    } else {
                        xh37s = "'" + xh37 + "'";
                    }
                    if ((xh38 + "") == null || ("").equals((xh38 + ""))) {
                        xh38s = "null";
                    } else {
                        xh38s = "'" + xh38 + "'";
                    }
                    if ((xh39 + "") == null || ("").equals((xh39 + ""))) {
                        xh39s = "null";
                    } else {
                        xh39s = "'" + xh39 + "'";
                    }
                    if ((xh40 + "") == null || ("").equals((xh40 + ""))) {
                        xh40s = "null";
                    } else {
                        xh40s = "'" + xh40 + "'";
                    }
                    if ((xh41 + "") == null || ("").equals((xh41 + ""))) {
                        xh41s = "null";
                    } else {
                        xh41s = "'" + xh41 + "'";
                    }
                    if ((xh42 + "") == null || ("").equals((xh42 + ""))) {
                        xh42s = "null";
                    } else {
                        xh42s = "'" + xh42 + "'";
                    }
                    if ((xh43 + "") == null || ("").equals((xh43 + ""))) {
                        xh43s = "null";
                    } else {
                        xh43s = "'" + xh43 + "'";
                    }
                    if ((xh44 + "") == null || ("").equals((xh44 + ""))) {
                        xh44s = "null";
                    } else {
                        xh44s = "'" + xh44 + "'";
                    }
                    if ((xh45 + "") == null || ("").equals((xh45 + ""))) {
                        xh45s = "null";
                    } else {
                        xh45s = "'" + xh45 + "'";
                    }
                    if ((xh46 + "") == null || ("").equals((xh46 + ""))) {
                        xh46s = "null";
                    } else {
                        xh46s = "'" + xh46 + "'";
                    }
                    if ((xh47 + "") == null || ("").equals((xh47 + ""))) {
                        xh47s = "null";
                    } else {
                        xh47s = "'" + xh47 + "'";
                    }
                    if ((xh48 + "") == null || ("").equals((xh48 + ""))) {
                        xh48s = "null";
                    } else {
                        xh48s = "'" + xh48 + "'";
                    }
                    if ((xh49 + "") == null || ("").equals((xh49 + ""))) {
                        xh49s = "null";
                    } else {
                        xh49s = "'" + xh49 + "'";
                    }
                    if ((xh50 + "") == null || ("").equals((xh50 + ""))) {
                        xh50s = "null";
                    } else {
                        xh50s = "'" + xh50 + "'";
                    }
                    if ((xh51 + "") == null || ("").equals((xh51 + ""))) {
                        xh51s = "null";
                    } else {
                        xh51s = "'" + xh51 + "'";
                    }
                    if ((xh52 + "") == null || ("").equals((xh52 + ""))) {
                        xh52s = "null";
                    } else {
                        xh52s = "'" + xh52 + "'";
                    }
                    if ((xh53 + "") == null || ("").equals((xh53 + ""))) {
                        xh53s = "null";
                    } else {
                        xh53s = "'" + xh53 + "'";
                    }
                    if ((xh54 + "") == null || ("").equals((xh54 + ""))) {
                        xh54s = "null";
                    } else {
                        xh54s = "'" + xh54 + "'";
                    }
                    if ((xh55 + "") == null || ("").equals((xh55 + ""))) {
                        xh55s = "null";
                    } else {
                        xh55s = "'" + xh55 + "'";
                    }
                    if ((xh56 + "") == null || ("").equals((xh56 + ""))) {
                        xh56s = "null";
                    } else {
                        xh56s = "'" + xh56 + "'";
                    }
                    if ((xh57 + "") == null || ("").equals((xh57 + ""))) {
                        xh57s = "null";
                    } else {
                        xh57s = "'" + xh57 + "'";
                    }
                    String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
                    Random rands = new Random();
                    int randomNumber = rands.nextInt(999999);
                    String a1 = (xh3s.replace("'", "")).replace("-", "");
                    String a2 = a1.replace(" ", "");
                    String a3 = a2.replace(":", "");
                    String a4 = tableHead.replace("_", "");
                    String time = (xh3s.replace("'", "")).substring(0, 10);
                    String idd = "";
                    idd = a4 + randomNumber + a3;
                    //客户信息
                    String id = "";
                    String reservationsId = "";
                    String selectsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where medical_record_number=" + xh4s + " " +
                            " and arrival_time is not null order by arrival_time desc limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    if (customerInfo == null) {
                        String selectsql11 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where customer_name=" + xh5s + " and customer_phone=" + xh8s + "" +
                                " and arrival_time is not null order by arrival_time desc limit 0,1;";
                        customerInfo = sqlMapper.selectOne(selectsql11, CustomerInfo.class);
                        if (customerInfo == null) {
                            String selectsql12 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where customer_name=" + xh5s + "  " +
                                    " and left(arrival_time,10)='" + time + "' limit 0,1;";
                            customerInfo = sqlMapper.selectOne(selectsql12, CustomerInfo.class);
                        }
                    }
                    String selectsql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where medical_record_number=" + xh4s + " " +
                            " and left(arrival_time,10)='" + time + "' limit 0,1;";  //到店时间等于结账时间
                    CustomerInfo customerInfo1 = sqlMapper.selectOne(selectsql1, CustomerInfo.class);
                    if (customerInfo1 == null) {
                        String selectsql11 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where customer_name=" + xh5s + " and customer_phone=" + xh8s + "" +
                                " and left(arrival_time,10)='" + time + "' limit 0,1;";  //到店时间等于结账时间
                        customerInfo1 = sqlMapper.selectOne(selectsql11, CustomerInfo.class);
                    }
                    if (customerInfo1 == null) {
                        if (customerInfo == null) {
                        } else {
                            customerInfo1 = customerInfo;
                        }
                    }
                    if (customerInfo1 == null || customerInfo1.equals("")) {
                        id = idd;
                        reservationsId = "预约" + idd;
                    } else {
                        id = customerInfo.getId();
                        reservationsId = customerInfo.getReservationsId();
                    }
                    String payId = "";
                    String id2 = "";
                    //收费项目
                    String sqlPayService = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " where pay_service_name=" + xh20s + " " +
                            " limit 0,1;";
                    PayServiceInfo payService = sqlMapper.selectOne(sqlPayService, PayServiceInfo.class);
                    if (payService == null) {
                        String insertsql = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " (`num`,`department`, `first_level_project`, " +
                                "`pay_service_name`, `pay_service_price_one`, `pay_service_price_two`,is_upper,remarks) VALUES" +
                                "(  " + 8989 + " ,'牙医管家健吉店'," + xh21s + " " +
                                "," + xh20s + "," + xh17s + "," + xh17s + ",0 ,'2020年01月18日健吉店导入');";
                        sqlMapper.insert(insertsql);
                        String sqlPayService1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_info",0) + " where pay_service_name=" + xh20s + " " +
                                " limit 0,1;";
                        PayServiceInfo payService1 = sqlMapper.selectOne(sqlPayService1, PayServiceInfo.class);
                        payId = payService1.getId();
                    } else {
                        payId = payService.getId();
                    }
                    //收费类型
                    if ("'退款'".equals(xh23s)) {
                        xh23s = "'4'";
                    } else if ("'正常'".equals(xh23s) && "'0.0'".equals(xh29s) && "'0.0'".equals(xh17s) && "'0.0'".equals(xh18s) && "'0.0'".equals(xh19s)) {
                        xh23s = "'3'";
                    } else if ("'正常'".equals(xh23s)) {
                        xh23s = "'5'";
                    } else {
                        xh23s = "'0'";
                    }
                    RechargeInfo rechargeInfo = new RechargeInfo();
                    rechargeInfo = null;
                    if (("'正常'".equals(xh23s) && "'0'".equals(xh29s) && "'0'".equals(xh17s) && "'0'".equals(xh18s) && "'0'".equals(xh19s)) || "'退费'".equals(xh23s)) {
                        String selnsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " a where a.reservations_id='" + reservationsId + "' and pay_service_id ='" + payId + "' and operation_time=get_time limit 1";
                        rechargeInfo = sqlMapper.selectOne(selnsql, RechargeInfo.class);
                    }
                    String sql = "";
                    String deductionType = "";
                    String deductionAmount = "null";
//                     if (!"'0.0'".equals(xh53s) && !"null".equals(xh53s)) {
//                        deductionType = "会员卡";
//                        deductionAmount = xh53s;
//                    }
                    if (rechargeInfo != null) {
                        if (customerInfo1 != null) {
                            randomNumber = randomNumber + 1;
                            idd = rechargeInfo.getId2() + randomNumber;
                            id = customerInfo1.getId();
                            id2 = rechargeInfo.getId2();
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time," +
                                    "get_time,remarks,doctor,nurse,orthodontic_number,whole_discount," +
                                    "id2,deduction_type,deduction_amount ,left_up) VALUES " +
                                    "('" + idd + "' , " + xh23s + " , '" + customerInfo1.getId() + "' , '健吉店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh33s + "," + xh18s + " , " +
                                    " " + xh31s + " , " + xh29s + " , " + xh17s + ", " + xh38s + " ,'" + rechargeInfo.getOperationTime() + "' ,  " +
                                    " " + xh3s + " ," + xh44s + " , " + xh25s + " , " + xh26s + " , " + xh36s + " , " + xh30s + " , " +
                                    " '" + rechargeInfo.getId2() + "','" + deductionType + "'," + deductionAmount + "," + xh37s + ");";
                        } else {
                            randomNumber = randomNumber + 1;
                            idd = rechargeInfo.getId2() + randomNumber;
                            id = idd;
                            id2 = rechargeInfo.getId2();
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time," +
                                    "get_time,remarks,doctor,nurse,orthodontic_number,whole_discount," +
                                    "id2,deduction_type,deduction_amount ,left_up) VALUES " +
                                    "('" + idd + "' , " + xh23s + " , '" + idd + "' , '健吉店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh33s + "," + xh18s + " , " +
                                    " " + xh31s + " , " + xh29s + " , " + xh17s + ", " + xh38s + " ,'" + rechargeInfo.getOperationTime() + "' ,  " +
                                    " " + xh3s + " ," + xh44s + " , " + xh25s + " , " + xh26s + " , " + xh36s + " , " + xh30s + " , " +
                                    " '" + rechargeInfo.getId2() + "','" + deductionType + "'," + deductionAmount + "," + xh37s + ");";
                        }
                    } else {
                        if (customerInfo1 == null) {
                            id2 = idd;
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time," +
                                    "get_time,remarks,doctor,nurse,orthodontic_number,whole_discount," +
                                    "id2,deduction_type,deduction_amount ,left_up) VALUES " +
                                    "('" + idd + "' , " + xh23s + " , '" + id + "' , '健吉店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh33s + "," + xh18s + " , " +
                                    " " + xh31s + " , " + xh29s + " , " + xh17s + ", " + xh38s + " ,'" + xh2s + "' ,  " +
                                    " " + xh3s + " ," + xh44s + " , " + xh25s + " , " + xh26s + " , " + xh36s + " , " + xh30s + " , " +
                                    " '" + id2 + "','" + deductionType + "'," + deductionAmount + "," + xh37s + ");";
                        } else {
                            id = customerInfo1.getId();
                            id2 = idd;
                            sql = "insert into " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"deal_info",0) + " " +
                                    "( id,is_do, customer_id,outpatient_name,reservations_id,pay_service_id,deal_status,tc_name,pay_service_num," +
                                    "service_actual_pay,folding_pay,pay_service,arrears,operation_time," +
                                    "get_time,remarks,doctor,nurse,orthodontic_number,whole_discount," +
                                    "id2,deduction_type,deduction_amount,left_up ) VALUES " +
                                    "('" + idd + "' , " + xh23s + " , '" + customerInfo1.getId() + "' , '健吉店' , '"
                                    + reservationsId + "', '" + payId + "', '已结账' , " + xh33s + "," + xh18s + " , " +
                                    " " + xh31s + " , " + xh29s + " , " + xh17s + ", " + xh38s + " ,'" + xh2s + "' ,  " +
                                    " " + xh3s + " ," + xh44s + " , " + xh25s + " , " + xh26s + " , " + xh36s + " , " + xh30s + " , " +
                                    " '" + id2 + "','" + deductionType + "'," + deductionAmount + "," + xh37s + ");";
                        }
                    }
                    int count = 0;
                    if (!"'0.0'".equals(xh45s) && !"null".equals(xh45s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','银行卡'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh45s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh46s) && !"null".equals(xh46s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','支付宝'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh46s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh47s) && !"null".equals(xh47s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','微信'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh47s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh48s) && !"null".equals(xh48s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','孝感优选'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh48s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh49s) && !"null".equals(xh49s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','云客赞'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh49s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh50s) && !"null".equals(xh50s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','美团'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh50s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh51s) && !"null".equals(xh51s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','社保卡'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh51s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh52s) && !"null".equals(xh52s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','现金'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh52s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }
                    if (!"'0.0'".equals(xh53s) && !"null".equals(xh53s)) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','会员卡'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh53s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                        count++;
                    }

                    if (count == 0) {
                        String sqlinsert1 = "INSERT INTO " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " (`recharge_people`,`customer_id`," +
                                "`checkout_method`,`status`,`reservations_id`,  `record_people`, `actual_account_balance`," +
                                "`recharge_time`,`operation_time`,remarks,document_number,id2,deal_id,isEarnes) VALUES" +
                                "(" + xh28s + ",'" + id + "','现金'," + 2 + ",'" + reservationsId + "'," + xh28s + "," +
                                "" + xh47s + "," + xh3s + "," + xh3s + "," + xh44s + " ," + xh16s + ",'" + id2 + "','" + id2 + "',1);";
                        sqlMapper.insert(sqlinsert1);
                    }
                    sqlMapper.insert(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return PREFIX + "testClose.html";
        } else {
            return PREFIX + "testClose.html";
        }
    }


    @RequestMapping(value = "/arrearInfo_import")
    public String arrearInfo_import(Model model) {
        return PREFIX + "arrearInfo_import.html";
    }

    /**
     * 导入
     */
    @RequestMapping(value = "/uploadArrears")
    public String uploadArrears(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);
                    Cell xh4 = hssfRow.getCell(3);

                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";
                    String xh4s = "";

                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    if ((xh4 + "") == null || ("").equals((xh4 + ""))) {
                        xh4s = "null";
                    } else {
                        xh4s = "'" + xh4 + "'";
                    }

                    //客户信息
                    /*String selectsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where customer_name=" + xh1s + " " +
                            " limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);*/

                    String sql = "update  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "" +
                            " set actual_account_balance=" + xh3s + ",account_arrears=" + xh4s + " where customer_name= " + xh1s + "";
                    sqlMapper.update(sql);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }


    @RequestMapping(value = "/medicalNumber_import")
    public String medicalNumber_import(Model model) {
        return PREFIX + "medicalNumber_import.html";
    }

    /**
     * 导入病历号
     */
    @RequestMapping(value = "/uploadMedicalNumber")
    public String uploadMedicalNumber(@Valid MultipartFile file) {
        if (!StringUtil.isEmpty(file)) {
            Random random = new Random();
            String realPath = "D:\\yiyatmp\\" + random.nextInt(100);
            String FileInput = realPath + "\\" + file.getOriginalFilename();
            try {
                FileUtils.copyInputStreamToFile(file.getInputStream(), new File(realPath, file.getOriginalFilename()));
                InputStream is = new FileInputStream(FileInput);
                HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
                // 循环工作表Sheet
                Sheet hssfSheet = hssfWorkbook.getSheetAt(0);
                Row hssfRow0 = hssfSheet.getRow(0);
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {
                        continue;
                    }
                    Cell xh1 = hssfRow.getCell(0);
                    Cell xh2 = hssfRow.getCell(1);
                    Cell xh3 = hssfRow.getCell(2);

                    String xh1s = "";
                    String xh2s = "";
                    String xh3s = "";

                    if ((xh1 + "") == null || ("").equals((xh1 + ""))) {
                        xh1s = "null";
                    } else {
                        xh1s = "'" + xh1 + "'";
                    }
                    if ((xh2 + "") == null || ("").equals((xh2 + ""))) {
                        xh2s = "null";
                    } else {
                        xh2s = "'" + xh2 + "'";
                    }
                    if ((xh3 + "") == null || ("").equals((xh3 + ""))) {
                        xh3s = "null";
                    } else {
                        xh3s = "'" + xh3 + "'";
                    }
                    /*if (xh3.toString() != null) {
                        xh3s = xh3.toString();
                    } else {
                        xh3s = null;
                    }
*/
                 /*   //客户信息
                    String selectsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where customer_phone like '%" + xh3s + "%'" +
                            "  and customer_name= "+xh2s+" limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    if(customerInfo!=null){
                        String sql = "update  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "" +
                                " set medical_record_number=" + xh1s + " where customer_phone like '%" + xh3s + "%'  and customer_name= "+xh2s+"";
                        sqlMapper.update(sql);
                    }*/

                    //客户信息
                    String selectsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + " where   customer_name= " + xh2s + " limit 0,1;";
                    CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
                    if (customerInfo != null) {
                        String sql = "update  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"customer_info",0) + "" +
                                " set medical_record_number=" + xh1s + " where  customer_name= " + xh2s + "";
                        sqlMapper.update(sql);
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return "/testClose.html";
        } else {
            return "/testClose.html";
        }
    }

    @RequestMapping(value = "/updateDocumentNumber", method = RequestMethod.POST)
    @ResponseBody
    public int updateDocumentNumber(String getTime, String oldDocumentNumber, String documentNumber, String customerId) {
        String remark = ShiroKit.getUser().getAccount() + "在" + DateTimeKit.now() + "把单据号：" + oldDocumentNumber + "修改为：" + documentNumber;
        String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"recharge_info",0) + " set document_number='" + documentNumber + "'," +
                "remarkss='" + remark + "' where customer_id='" + customerId + "'  and operation_time='" + getTime + "'";
        return sqlMapper.update(sql);
    }

}
  